23. supabase-llm-price-settings.sql

원본 파일: 'C:\Repository\loafacto-hub\docs\web-ui\23. supabase-llm-price-settings.sql'
sql
-- ============================================================
-- LLM 모델 과금 단가 설정 테이블 (llm_price_settings)
-- AdminSettings에서 관리자가 모델별 단가 설정
-- MyInfoPage에서 사용자 과금 인지용 예상 비용 계산에 사용
-- ============================================================

CREATE TABLE IF NOT EXISTS public.llm_price_settings (
  id           uuid          DEFAULT gen_random_uuid() PRIMARY KEY,
  model        text          NOT NULL UNIQUE,          -- 모델명 (e.g. gpt-4o-mini)
  provider     text          NOT NULL DEFAULT 'openai',-- 공급자
  input_per_1k numeric(10,6) NOT NULL DEFAULT 0,       -- $ per 1K input tokens
  output_per_1k numeric(10,6) NOT NULL DEFAULT 0,      -- $ per 1K output tokens
  currency     text          NOT NULL DEFAULT 'USD',
  updated_at   timestamptz   DEFAULT now() NOT NULL
);

-- 인덱스
CREATE INDEX IF NOT EXISTS llm_price_settings_model_idx
  ON public.llm_price_settings (model);

-- RLS
ALTER TABLE public.llm_price_settings ENABLE ROW LEVEL SECURITY;

-- 모든 로그인 사용자 조회 (비용 계산용)
DROP POLICY IF EXISTS "auth_read_llm_prices" ON public.llm_price_settings;
CREATE POLICY "auth_read_llm_prices" ON public.llm_price_settings
  FOR SELECT USING (auth.uid() IS NOT NULL);

-- super_admin / operator 만 INSERT/UPDATE/DELETE
DROP POLICY IF EXISTS "admin_write_llm_prices" ON public.llm_price_settings;
CREATE POLICY "admin_write_llm_prices" ON public.llm_price_settings
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM public.user_roles
      WHERE user_id = auth.uid()
        AND role IN ('super_admin', 'operator')
    )
  );

-- ============================================================
-- 초기 데이터 (OpenAI 공개 단가 기준, 필요 시 조정)
-- ============================================================
INSERT INTO public.llm_price_settings (model, provider, input_per_1k, output_per_1k) VALUES
  ('gpt-4o',             'openai', 0.005000, 0.015000),
  ('gpt-4o-mini',        'openai', 0.000150, 0.000600),
  ('gpt-4-turbo',        'openai', 0.010000, 0.030000),
  ('gpt-3.5-turbo',      'openai', 0.000500, 0.001500)
ON CONFLICT (model) DO NOTHING;
23. supabase-llm-price-settings.sql ​

23. supabase-llm-price-settings.sql
